const db = require("../utils/db");
const util = require("../utils/util")

async function add_message(f_user_id, t_user_id, title, content, link) { 
    var sql = `insert into message(f_user_id, t_user_id, title, content, readed, link, message_time)
                values(?,?,?,?,?,?,?)`;
    var args = [f_user_id, t_user_id, title, content, 0, link, util.datestring(new Date())];
    return (await db.query(sql, args)).insertId;
}

async function del_message(message_id) {
    var sql = `delete from message where message_id = ${message_id}`;
    await db.query(sql);
}

async function del_read_message(user_id) {
    var sql = `delete from message where t_user_id=${user_id} and readed=1`;
    await db.query(sql);
}

async function read_message(message_id) {
    var sql = `update message set readed=1 where message_id = ${message_id}`;
    await db.query(sql);
}

async function get_message_list(user_id) {
    var sql =
`
SELECT
	message.*,
	user.name AS user_name,
	user.avatar AS user_avatar
FROM message, user
WHERE
	message.f_user_id = user.user_id and
    message.t_user_id = '${user_id}'
ORDER BY readed ASC, message_time DESC
`
    return await db.query(sql);
}

async function get_unread_num(user_id) { 
    var sql = 
`
SELECT
	count(*) as count
FROM message
WHERE
	message.t_user_id = '${user_id}' and
message.readed = 0
`
    return (await db.query(sql))[0].count;
}

async function get_user_id(message_id) {
    // 从message_id获取t_user_id，用以身份验证
    var sql = `SELECT t_user_id from message where message_id = ${message_id}`
    return (await db.query(sql))[0].t_user_id;
}

module.exports = {
    add_message: add_message,
    del_message: del_message,
    del_read_message: del_read_message,
    read_message: read_message,
    get_message_list: get_message_list,
    get_unread_num: get_unread_num,
    get_user_id: get_user_id,
}
